<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> 
<html><head> 
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
  <title>整合 SQLite UDF (用户定义函数) 到 webpy 数据库层 (web.py)</title> 
  <link rel="stylesheet" type="text/css" href="/static/webpy-new.css"/> 
  <link rel="SHORTCUT ICON" href="/static/favicon.ico"/> 
</head><body> 
 
        <div id="header"><table class="header"><tr>
                <td class="logo"><a href="/" title="Home"><img src="/static/webpy.gif" alt="" /></a></td> 
<td class="blurb">&ldquo;Think about the ideal way to write a web app.<br />Write the code to make it happen.&rdquo; [<a href="/philosophy">more...</a>]</td> </tr>
</table>
        </div> 
        <div id="location"> 
                <a href="/">home</a> &gt; 整合 SQLite UDF (用户定义函数) 到 webpy 数据库层
        </div> 
<div id="container"> 
<div id="sidebar">
<strong>get started:</strong>
<ul class="body">
  <li><a href="/install">install</a></li>
  <li><a href="/docs/0.3/tutorial">tutorial</a></li>
</ul>
<strong>learn more:</strong>
<ul class="body">
<li><a href="/docs/0.3/api">api reference</a></li>
<li><a href="/cookbook/">cookbook</a></li>
<li><a href="/src/">code examples</a></li>
</ul>

<strong>dive in:</strong>
<ul class="body"> 
<li><a href="https://github.com/webpy/webpy">source code</a></li>
<li><a href="http://groups.google.com/group/webpy/">mailing list</a></li>
<li><a href="https://github.com/webpy/webpy/issues">issue tracker</a></li>
</ul>
</div>

<div id="main"> 
<div id="content" class="body"> 
<h1>整合 SQLite UDF (用户定义函数) 到 webpy 数据库层</h1>

<p>问题：</p>

<p>用户在邮件列表中询问，我把它放在这里作为将来使用和参考。</p>

<p>解决：</p>

<p>您可以添加到Python函数到SQLite，并让它们在您的查询调用。</p>

<p>示例：</p>

<pre>
>>> import sqlite3 as db
>>> conn = db.connect(":memory:")
>>> conn.create_function("sign", 1, lambda val: val and (val > 0 and 1 or -1))
>>> cur = conn.cursor()
>>> cur.execute("select 1, -1")
&lt;sqlite3.Cursor object at 0xb759f2c0&gt;
>>> print cur.fetchall()
[(1, -1)]
>>> cur.execute("select sign(1), sign(-1), sign(0), sign(-99), sign(99)")
&lt;sqlite3.Cursor object at 0xb759f2c0&gt;
>>> print cur.fetchall()
[(1, -1, 0, -1, 1)]
>>> conn.close()</pre>


<p>在webpy中，你可以通过游标如db._db_cursor().connection 取得连接对象的引用。</p>

<p>示例：</p>

<pre>
>>> import web
>>> db = web.database(dbn="sqlite", db=":memory:")
>>> db._db_cursor().connection.create_function("sign", 1, lambda val: val and (val > 0 and 1 or -1))
>>> print db.query("select sign(1), sign(-1), sign(0), sign(-99), sign(99)").list()
[&lt;Storage {'sign(1)': 1, 'sign(-1)': -1, 'sign(99)': 1, 'sign(-99)': -1, 'sign(0)': 0}&gt;]
</pre>


<div style="clear: both;"></div> 
 
</div> 
 
</div> 
</div>
        <div id="footer"> 
    <p>started by <a style="color: gray;" href="http://www.aaronsw.com/">Aaron Swartz</a> (<a style="color: black;" href="mailto:webpy@aaronsw.com">webpy@aaronsw.com</a>)</p>

<p><a href="https://github.com/webpy/webpy.github.com/blob/master/cookbook/sqlite-udf.zh-cn.md">fork this page on GitHub</a></p>
</div> 
 
</body> 
</html> 
